/**
 * 
 */
package com.nari.heatshare.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;

import com.nari.coreframe.dao.hibernate3.GenericDaoImpl;
import com.nari.heatshare.model.ITemperaturePointDataGather;
import com.nari.heatshare.utils.queryvo.TempPointDataCustomizedQuery;
import com.nari.platform.date.CalendarUtils;
import com.nari.platform.model.PageCond;
import com.nari.platform.model.ResponseMsg;
import com.nari.platform.model.ResultWithPageCond;
import com.nari.platform.utils.StringUtils;
import com.nari.rescontroller.dao.IResourceAccessControlDao;

/**
 * 
 * @author Xiaopeng
 * @date 2014-3-14
 */
public class TemperaturePointDataGatherDaoImpl extends GenericDaoImpl<ITemperaturePointDataGather, String>
		implements ITemperaturePointDataGatherDao {
	//数据库本地化配置
	private String sysDbName;
	private String oracleDbName;
	private String mysqlDbName;
	private IResourceAccessControlDao iResourceAccessControlDao;
	
	/**
	 * 根据温度点编号、所属集中器号、抄表时间查询温度点数据的唯一记录
	 * @param seriousNum
	 * @param concentratorNo
	 * @param recordTime
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-3-17
	 */
	@SuppressWarnings("unchecked")
	public ITemperaturePointDataGather queryUniqueTempPointData(String seriousNum, String concentratorNo, 
			Date recordTime) throws Exception {
		String hsql = "from ITemperaturePointDataGather tpd " +
				"where tpd.seriousNum='"+seriousNum+"' " +
				"and tpd.concentratorNo='"+concentratorNo+"'";
		//设置查询的抄表时间范围
		Date recordTimeMin = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Date recordTimeMax = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		hsql += " and tpd.recordTime between :startTime and :endTime";
		Query query = this.getCurrentSession().createQuery(hsql);
		query.setTimestamp("startTime", recordTimeMin);
		query.setTimestamp("endTime", recordTimeMax);
		//查询温度点数据
		List<ITemperaturePointDataGather> tempPointDataList = query.list();
		if(tempPointDataList!=null && tempPointDataList.size()>0) {
			return tempPointDataList.get(0);
		}
		return null;
	}

	/**
	 * 新增或修改温度点数据
	 * @param tempPointData
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-3-17
	 */
	public ResponseMsg saveOrUpdateTempPointData(ITemperaturePointDataGather tempPointData) throws Exception {
		//查询已归档的温度点数据
		ITemperaturePointDataGather archivedData = this.queryUniqueTempPointData(tempPointData.getSeriousNum(), 
				tempPointData.getConcentratorNo(), tempPointData.getRecordTime());
		//若对应特征信息的温度点数据已归档，则更新数据
		if(archivedData != null) {
			tempPointData.setId(archivedData.getId());
			this.updateEntity(tempPointData, archivedData.getId());
		} else { //新增温度点数据
			this.saveEntity(tempPointData);
		}
		return ResponseMsg.getInstance(true, "");
	}
	
	/**
	 * 根据集中器号、抄表时间查询一个上传批次的温度点数据
	 * @param concentratorNo
	 * @param recordTime
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-3-17
	 */
	@SuppressWarnings("unchecked")
	public List<ITemperaturePointDataGather> queryUploadBatchTempPointData(String concentratorNo, 
			Date recordTime) throws Exception {
		String hsql = "from ITemperaturePointDataGather tpd " +
				"where tpd.concentratorNo='"+concentratorNo+"'";
		//设置查询的抄表时间范围
		Date recordTimeMin = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Date recordTimeMax = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		hsql += " and tpd.recordTime between :startTime and :endTime " +
				"order by tpd.seriousNum asc";
		Query query = this.getCurrentSession().createQuery(hsql);
		query.setTimestamp("startTime", recordTimeMin);
		query.setTimestamp("endTime", recordTimeMax);
		return query.list();
	}
	
	/**
	 * 统计集中器上传的有效温度点数据（避免档案资料中不存在的温度点信息被统计）
	 * @param concentratorNo
	 * @param recordTime
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-3-17
	 */
	public int countValidUploadBatch(String concentratorNo, Date recordTime) throws Exception {
		String hsql = "select count(*) " +
				"from ITemperaturePointDataGather tpd " +
				"where tpd.concentratorNo='"+concentratorNo+"' " +
				"and tpd.recordTime between :startTime and :endTime " +
				"and tpd.seriousNum in(select tp.seriousNum " +
									"from ITemperaturePoint tp " +
									"where tp.concentratorNo='"+concentratorNo+"')";
		//设置查询的抄表时间范围
		Date recordTimeMin = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Date recordTimeMax = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Query query = this.getCurrentSession().createQuery(hsql);
		query.setTimestamp("startTime", recordTimeMin).setTimestamp("endTime", recordTimeMax);
		return ((Long)query.uniqueResult()).intValue();
	}
	
	/**
	 * 根据温度点数据列表及集中器号查询其对应的温度点编号-房间号映射表
	 * @param tempPointDataList
	 * @param concentratorNo
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-3-23
	 */
	public Map<String, String> queryReferHouseNoMap(List<ITemperaturePointDataGather> tempPointDataList, 
			String concentratorNo) throws Exception {
		Map<String, String> referMap = new HashMap<String, String>();
		if(tempPointDataList!=null && tempPointDataList.size()>0) {
			String hsql = "select tp.concentratorNo,tp.seriousNum,tp.houseNo " +
					"from ITemperaturePoint tp " +
					"where tp.concentratorNo='"+concentratorNo+"' " +
					"and tp.seriousNum in(";
			for(int index=0; index<tempPointDataList.size(); index++) {
				hsql += "'"+tempPointDataList.get(index).getSeriousNum()+"'";
				if(index < tempPointDataList.size()-1) {
					hsql += ",";
				}
			}
			hsql += ")";
			List<Object> resultsList = this.runQueryHSqlObject(hsql.toString());
			//格式化查询结果集
			if(resultsList!=null && resultsList.size()>0) {
				for(Object result: resultsList) {
					Object[] arrayMap = (Object[]) result;
					referMap.put(arrayMap[0].toString()+"-"+arrayMap[1].toString(), arrayMap[2].toString());
				}
			}
		}
		return referMap;
	}
	
	/**
	 * 根据温度点数据列表查询其对应的温度点编号-房间号映射表
	 * @param tempPointDataList
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-4-11
	 */
	public Map<String, String> queryReferHouseNoMap(List<ITemperaturePointDataGather> 
			tempPointDataList) throws Exception {
		Map<String, String> referMap = new HashMap<String, String>();
		if(tempPointDataList!=null && tempPointDataList.size()>0) {
			String hsql = "select tp.concentratorNo,tp.seriousNum,tp.houseNo " +
					"from ITemperaturePoint tp " +
					"where tp.concentratorNo in(";
			//集中器信息
			String concentratorNo = tempPointDataList.get(0).getConcentratorNo();
			hsql +="'"+concentratorNo+"',";
			for(int index=0; index<tempPointDataList.size(); index++) {
				if(!concentratorNo.equals(tempPointDataList.get(index).getConcentratorNo())) {
					hsql += "'"+tempPointDataList.get(index).getConcentratorNo()+"',";
				}
			}
			hsql = hsql.substring(0, hsql.length()-1);
			//温度点编号信息
			hsql += ") and tp.seriousNum in(";
			for(int index=0; index<tempPointDataList.size(); index++) {
				hsql += "'"+tempPointDataList.get(index).getSeriousNum()+"'";
				if(index < tempPointDataList.size()-1) {
					hsql += ",";
				}
			}
			hsql += ")";
			List<Object> resultsList = this.runQueryHSqlObject(hsql.toString());
			//格式化查询结果集
			if(resultsList!=null && resultsList.size()>0) {
				for(Object result: resultsList) {
					Object[] arrayMap = (Object[]) result;
					referMap.put(arrayMap[0].toString()+"-"+arrayMap[1].toString(), arrayMap[2].toString());
				}
			}
		}
		return referMap;
	}
	
	/**
	 * 根据任务信息(集中器号、抄表时间)删除温度点数据批次
	 * @param concentratorNo
	 * @param recordTime
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-4-9
	 */
	public void deleteTempPointDataBatch(String concentratorNo, Date recordTime) throws Exception {
		String hsql = "delete from ITemperaturePointDataGather tpd " +
				"where tpd.concentratorNo like '%"+concentratorNo.toLowerCase()+"%' " +
				"and tpd.recordTime between :startTime and :endTime";
		//设置查询的抄表时间范围
		Date recordTimeMin = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Date recordTimeMax = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Query query = this.getCurrentSession().createQuery(hsql);
		query.setTimestamp("startTime", recordTimeMin).setTimestamp("endTime", recordTimeMax);
		query.executeUpdate();
	}
	
	/**
	 * 查询缺少基础档案信息的温度点数据对应的表号
	 * @param concentratorNo
	 * @param recordTime
	 * @param dataType
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-4-9
	 */
	@SuppressWarnings("unchecked")
	public List<String> queryLackBaseinfoSeriousNum(String concentratorNo, Date recordTime,
			Integer dataType) throws Exception {
		//查询档案中不存在表号的抄表数据
		String hsql = "select distinct tpd.seriousNum from ITemperaturePointDataGather tpd " +
				"where lower(tpd.concentratorNo)='"+concentratorNo.toLowerCase()+"' " +
				"and tpd.recordTime between :startDate and :endDate " +
				"and lower(tpd.seriousNum) not in(select lower(tp.seriousNum) " +
												"from ITemperaturePoint tp " +
												"where tp.concentratorID=(select c.id " +
																		"from IConcentrator c " +
																		"where lower(c.concentratorNo)='"+concentratorNo.toLowerCase()+"'))";
		//数据类型
		if(dataType != null) {
			hsql += " and tpd.dataType="+dataType;
		}
		//结果集排序
		hsql += " order by tpd.seriousNum asc";
		Date startDate = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Date endDate = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Query query = this.getCurrentSession().createQuery(hsql);
		query.setTimestamp("startDate", startDate).setTimestamp("endDate", endDate);
		return query.list();
	}
	
	/**
	 * 分页查询温度点数据定制查询列表
	 * @param projectUID
	 * @param communityUID
	 * @param buildingUID
	 * @param recordTime
	 * @param pageCond
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-4-11
	 */
	@SuppressWarnings("unchecked")
	public ResultWithPageCond<TempPointDataCustomizedQuery> queryCustomizedQueryPage(String projectUID, 
			String communityUID, String buildingUID, Date recordTime, 
			PageCond pageCond) throws Exception {
		String listQueryHsql = "";
		//温度点数据定制查询
		if(oracleDbName.equals(sysDbName)) {
			listQueryHsql = "select tp.projectID,tp.communityID,tp.buildingID," +
					"to_char(tpd.recordTime, 'YYYY-MM-DD') as recordDate,count(tpd.seriousNum)";
		} else if(mysqlDbName.equals(sysDbName)) {
			listQueryHsql = "select tp.projectID,tp.communityID,tp.buildingID," +
					"date_format(tpd.recordTime,'%Y-%m-%d') as recordDate,count(tpd.seriousNum)";
		}
		listQueryHsql += " from ITemperaturePoint tp, ITemperaturePointDataGather tpd";
		//权限资源匹配
		String authorityRes = iResourceAccessControlDao.queryAuthorityProjResHql();
		if(!"".equals(authorityRes)) { //非管理员用户授权项目权限
			List<Object> projectUIDList = this.runQueryHSqlObject(authorityRes);
			String formatProjectUIDList = StringUtils.formatIDsByList(projectUIDList);
			formatProjectUIDList = StringUtils.formatIDListWithSyntax(formatProjectUIDList, ",", "'");
			listQueryHsql += " where tp.projectID in("+formatProjectUIDList+") and ";
		} else { //管理员所有项目权限
			listQueryHsql += " where ";
		}
		//设置表关联
		listQueryHsql += "tp.concentratorNo=tpd.concentratorNo " + 
				"and tp.seriousNum=tpd.seriousNum"; 
		//设置查询字段
		if(projectUID!=null && !"".equals(projectUID)) {
			listQueryHsql += " and tp.projectID='"+projectUID+"'";
		}
		if(communityUID!=null && !"".equals(communityUID)) {
			listQueryHsql += " and tp.communityID='"+communityUID+"'";
		}
		if(buildingUID!=null && !"".equals(buildingUID)) {
			listQueryHsql += " and tp.buildingID='"+buildingUID+"'";
		}
		if(recordTime != null) {
			listQueryHsql += " and tpd.recordTime between :startTime and :endTime";
		}
		//定制查询分组
		if(oracleDbName.equals(sysDbName)) {
			listQueryHsql += " group by tp.buildingID,tp.communityID,tp.projectID,to_char(tpd.recordTime, 'YYYY-MM-DD') " +
					"having count(tpd.seriousNum)>0";
		} else if(mysqlDbName.equals(sysDbName)) {
			listQueryHsql += " group by tp.buildingID,tp.communityID,tp.projectID,date_format(tpd.recordTime,'%Y-%m-%d') " +
					"having count(tpd.seriousNum)>0";
		}
		//结果集个数查询
//		String totalQueryHsql = "select count(cus_list) from ("+listQueryHsql+") as cus_list"; //一个HQL语句中运行两个count函数会报错
		Query countQuery = null;
		Query listQuery = null;
		if(recordTime != null) {
			Date recordTimeStart = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
			Date recordTimeEnd = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
			countQuery = this.getCurrentSession().createQuery(listQueryHsql);
			countQuery.setTimestamp("startTime", recordTimeStart).setTimestamp("endTime", recordTimeEnd);
			//定制查询排序
			if(oracleDbName.equals(sysDbName)) {
				listQueryHsql += " order by to_char(tpd.recordTime, 'YYYY-MM-DD') desc";
			} else if(mysqlDbName.equals(sysDbName)) {
				listQueryHsql += " order by date_format(tpd.recordTime,'%Y-%m-%d') desc";
			}
			listQuery = this.getCurrentSession().createQuery(listQueryHsql);
			listQuery.setTimestamp("startTime", recordTimeStart).setTimestamp("endTime", recordTimeEnd);
		} else {
			countQuery = this.getCurrentSession().createQuery(listQueryHsql);
			//定制查询排序
			if(oracleDbName.equals(sysDbName)) {
				listQueryHsql += " order by to_char(tpd.recordTime, 'YYYY-MM-DD') desc";
			} else if(mysqlDbName.equals(sysDbName)) {
				listQueryHsql += " order by date_format(tpd.recordTime,'%Y-%m-%d') desc";
			}
			listQuery = this.getCurrentSession().createQuery(listQueryHsql);
		}
		int total = countQuery.list().size();
		//定制查询排序
		listQuery.setFirstResult(pageCond.getStart()).setMaxResults(pageCond.getLimit());
		//封装查询结果
		List<Object> queryResultList = listQuery.list();
		List<TempPointDataCustomizedQuery> customizedQueryList = new ArrayList<TempPointDataCustomizedQuery>();
		if(queryResultList!=null && queryResultList.size()>0) {
			for(Object queryResult: queryResultList) {
				Object[] customizedResultArray = (Object[]) queryResult;
				projectUID = (String) customizedResultArray[0];
				communityUID = (String) customizedResultArray[1];
				buildingUID = (String) customizedResultArray[2];
				String recordDate = (String) customizedResultArray[3];
				int tempPointDataCounter = ((Long)customizedResultArray[4]).intValue();
				String hsql = "select count(*) from ITemperaturePoint tp where tp.buildingID='"+buildingUID+"'";
				int tempPointNum = ((Long)this.getCurrentSession().createQuery(hsql).uniqueResult()).intValue();
				TempPointDataCustomizedQuery customizedQuery = TempPointDataCustomizedQuery.getInstance(projectUID, 
						communityUID, buildingUID, recordDate, tempPointNum, tempPointDataCounter);
				customizedQueryList.add(customizedQuery);
			}
		}
		//构造返回类型
		pageCond.setTotal(total);
		ResultWithPageCond<TempPointDataCustomizedQuery> customizedQueryPage = new ResultWithPageCond<TempPointDataCustomizedQuery>();
		customizedQueryPage.setList(customizedQueryList);
		customizedQueryPage.setPageCond(pageCond);
		return customizedQueryPage;
	}
	
	/**
	 * 根据定制查询特征信息(所属楼栋、抄表时间)统计温度点列表数据个数
	 * @param buildingUID
	 * @param recordTime
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-4-11
	 */
	public int countCustomizedTpDataList(String buildingUID, Date recordTime) throws Exception {
		String hsql = "select count(*) " +
				"from ITemperaturePointDataGather tpd, ITemperaturePoint tp " +
				"where tp.buildingID='"+buildingUID+"' " +
				"and tpd.recordTime between :startTime and :endTime " +
				"and tp.concentratorNo=tpd.concentratorNo " +
				"and tp.seriousNum=tpd.seriousNum";
		Date recordTimeStart = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Date recordTimeEnd = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Query query = this.getCurrentSession().createQuery(hsql);
		query.setTimestamp("startTime", recordTimeStart).setTimestamp("endTime", recordTimeEnd);
		return ((Long)query.uniqueResult()).intValue();
	}
	
	/**
	 * 根据定制查询特征信息(所属楼栋、抄表时间)查询温度点数据列表
	 * @param buildingUID
	 * @param recordTime
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-4-11
	 */
	@SuppressWarnings("unchecked")
	public List<ITemperaturePointDataGather> queryCustomizedTpDataList(String buildingUID, 
			Date recordTime) throws Exception {
		String hsql = "select tpd " +
				"from ITemperaturePointDataGather tpd, ITemperaturePoint tp " +
				"where tp.buildingID='"+buildingUID+"' " +
				"and tpd.recordTime between :startTime and :endTime " +
				"and tp.concentratorNo=tpd.concentratorNo " +
				"and tp.seriousNum=tpd.seriousNum " +
				"order by tpd.concentratorNo asc,tpd.seriousNum asc";
		Date recordTimeStart = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Date recordTimeEnd = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Query query = this.getCurrentSession().createQuery(hsql);
		query.setTimestamp("startTime", recordTimeStart).setTimestamp("endTime", recordTimeEnd);
		return query.list();
	}

	/**
	 * @return the sysDbName
	 */
	public String getSysDbName() {
		return sysDbName;
	}

	/**
	 * @param sysDbName the sysDbName to set
	 */
	public void setSysDbName(String sysDbName) {
		this.sysDbName = sysDbName;
	}

	/**
	 * @return the oracleDbName
	 */
	public String getOracleDbName() {
		return oracleDbName;
	}

	/**
	 * @param oracleDbName the oracleDbName to set
	 */
	public void setOracleDbName(String oracleDbName) {
		this.oracleDbName = oracleDbName;
	}

	/**
	 * @return the mysqlDbName
	 */
	public String getMysqlDbName() {
		return mysqlDbName;
	}

	/**
	 * @param mysqlDbName the mysqlDbName to set
	 */
	public void setMysqlDbName(String mysqlDbName) {
		this.mysqlDbName = mysqlDbName;
	}

	/**
	 * @return the iResourceAccessControlDao
	 */
	public IResourceAccessControlDao getiResourceAccessControlDao() {
		return iResourceAccessControlDao;
	}

	/**
	 * @param iResourceAccessControlDao the iResourceAccessControlDao to set
	 */
	public void setiResourceAccessControlDao(
			IResourceAccessControlDao iResourceAccessControlDao) {
		this.iResourceAccessControlDao = iResourceAccessControlDao;
	}

}
